set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions= 20;
set hive.exec.max.dynamic.partitions.pernode=20;

with tab_rou_main as (
    select
         city
        ,city_id
        ,area
        ,area_id
        ,aging_area_code
        ,aging_city_code
    from (
        select
             province
            ,province_id
            ,city
            ,city_id
            ,area
            ,area_id
            ,aging_area_code
            ,aging_city_code
        from jms_dim.dim_tab_rou_main_city
        where is_enable = 1
        and aging_area_code is not null
        and aging_city_code is not null
    ) t1
    left anti join (
        select city_id,area_id
        from jms_ods.tab_bulk_cargo_area_record
        where dt = '{{ execution_date | cst_ds }}'
        group by city_id,area_id
    ) t2
    on t2.city_id = t1.city_id
    and t2.area_id = t1.area_id
)

,dim_tab_bulk_cargo_area_record_base_dt as (
    select
         t2.create_date
        ,t2.regional_desc
        ,t2.regional_id
        ,t2.provider_desc
        ,t2.provider_id
        ,t2.agent_name
        ,t2.agent_code
        ,t2.center_name
        ,t2.center_code
        ,t1.city as city_desc           -- 市级(城市)名
        ,t1.city_id                     -- 市级(城市)编码
        ,t1.area as area_desc           -- 区县名
        ,t1.area_id                     -- 区县编码
        ,t2.first_time
        ,t2.second_time
        ,t2.deliver_over_time
        ,t2.deliver_span_day
        ,t2.dt
    from tab_rou_main t1
    left join (
        select *
        from jms_ods.tab_bulk_cargo_area_record
        where dt = '{{ execution_date | cst_ds }}'
    ) t2
    on t1.aging_area_code = t2.area_id
    and t1.aging_city_code = t2.city_id

    union all

    select
         create_date
        ,regional_desc
        ,regional_id
        ,provider_desc
        ,provider_id
        ,agent_name
        ,agent_code
        ,center_name
        ,center_code
        ,city_desc
        ,city_id
        ,area_desc
        ,area_id
        ,first_time
        ,second_time
        ,deliver_over_time
        ,deliver_span_day
        ,dt
    from jms_ods.tab_bulk_cargo_area_record
    where dt = '{{ execution_date | cst_ds }}'
)

insert overwrite table jms_dim.dim_tab_bulk_cargo_area_record_base_dt partition(dt)
select
     t1.create_date
    ,t1.regional_desc
    ,t1.regional_id
    ,t1.provider_desc
    ,t1.provider_id
    ,t1.agent_name
    ,t1.agent_code
    ,t1.center_name
    ,t1.center_code
    ,t1.city_desc
    ,t1.city_id
    ,t1.area_desc
    ,t1.area_id
    ,t1.first_time
    ,t1.second_time
    ,t1.deliver_over_time
    ,t1.deliver_span_day
    ,t2.is_main
    ,t1.dt
from dim_tab_bulk_cargo_area_record_base_dt t1
left join (
    select * from (
        select
             city_id
            ,area_id
            ,is_main
            ,row_number() over(partition by city_id,area_id order by modify_time desc) as row_id
        from jms_dim.dim_tab_rou_main_city
        where is_enable = 1
    ) tmp
    where row_id = 1
) t2
on t1.city_id = t2.city_id
and t1.area_id = t2.area_id
distribute by dt;

